go

exec dbo.p_drop_proc_if_exists 'meta_add_datetime_column';

go

--Function of adding new datetime column
create procedure meta_add_datetime_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);

    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_datetime_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);
        
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' datetime');
         
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
	commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_datetime_minimum';

go

--Function of setting datetime column minimum
create procedure meta_set_datetime_minimum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_minimum datetime)
with execute as owner
as
begin
	set nocount on;
	declare
    --Old minimum
    @a_old_minimum datetime,
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_datetime_columns', 'datetime', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_minimum = minimum,
    @a_customization_level = customization_level
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing datetime system column minimum is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_minimum = @a_old_minimum) or
        (@a_minimum is null and @a_old_minimum is null)
        return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_datetime_columns
    set minimum = @a_minimum
    where column_id = @a_column_id;
         
    --There is no need to drop table view
    
    if @a_old_minimum is not null
        --Drop old constraint
        exec dbo.p_drop_datetime_minimum_check @a_column_id;         
    
    if @a_minimum is not null
        exec dbo.p_create_datetime_minimum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_datetime_minimum_check';

go

--Function of creating datetime minimum check
create procedure p_create_datetime_minimum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Table name
	@a_table_name varchar(26),
	--Minimum
	@a_minimum datetime,
	--Minimum str
	@a_minimum_str varchar(64),
	--Column name
	@a_column_name varchar(26);
	
	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	--Reading column properties
	select @a_column_name = column_name,
	@a_minimum = minimum
	from meta_datetime_columns
	where column_id = @a_column_id;
	
	select @a_table_name = table_name
	from meta_table_columns
	where column_id = @a_column_id;
	
	set @a_minimum_str = convert(varchar, @a_minimum, 9);

	exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint n' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' >= ''' + @a_minimum_str + ''')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_datetime_minimum_check';

go

--Function of dropping datetime minimum check
create procedure p_drop_datetime_minimum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Table name
	@a_table_name varchar(26),
	--Column id str
	@a_column_id_str varchar(32);
	
	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	select @a_table_name = table_name
	from meta_table_columns
	where column_id = @a_column_id;
	
	exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint n' + @a_column_id_str); 
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_datetime_maximum';

go

--Function of setting datetime column maximum
create procedure meta_set_datetime_maximum(
    --Column id
    @a_column_id binary(16),
    --Minimum
    @a_maximum datetime)
with execute as owner
as
begin
	set nocount on;
	declare
    --Old maximum
    @a_old_maximum datetime,
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_datetime_columns', 'datetime', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_old_maximum = maximum,
    @a_customization_level = customization_level
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing datetime system column maximum is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_maximum = @a_old_maximum) or
        (@a_maximum is null and @a_old_maximum is null)
        return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_datetime_columns
    set maximum = @a_maximum
    where column_id = @a_column_id;
         
    --There is no need to drop table view
    
    if @a_old_maximum is not null
        --Drop old constraint
        exec dbo.p_drop_datetime_maximum_check @a_column_id;
    
    if @a_maximum is not null
		exec dbo.p_create_datetime_maximum_check @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_datetime_maximum_check';

go

--Function of creating datetime maximum check
create procedure p_create_datetime_maximum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Column id str
	@a_column_id_str varchar(32),
	--Table name
	@a_table_name varchar(26),
	--Maximum
	@a_maximum datetime,
	--Maximum str
	@a_maximum_str varchar(64),
	--Column name
	@a_column_name varchar(26);
	
	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	--Reading column properties
	select @a_column_name = column_name,
	@a_maximum = maximum
	from meta_datetime_columns
	where column_id = @a_column_id;
	
	select @a_table_name = table_name
	from meta_table_columns
	where column_id = @a_column_id;
	
	set @a_maximum_str = convert(varchar, @a_maximum, 9);

	exec ('alter table tbl_' 
    + @a_table_name
    + ' add constraint x' + @a_column_id_str
    + ' check(' + @a_column_name
    + ' <= ''' + @a_maximum_str + ''')');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_datetime_maximum_check';

go

--Function of dropping datetime maximum check
create procedure p_drop_datetime_maximum_check(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Table name
	@a_table_name varchar(26),
	--Column id str
	@a_column_id_str varchar(32);
	
	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
	
	select @a_table_name = table_name
	from meta_table_columns
	where column_id = @a_column_id;
	
	exec ('alter table tbl_' 
    + @a_table_name
    + ' drop constraint x' + @a_column_id_str); 
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_datetime_default';

go

--Function of setting column default value
create procedure meta_set_datetime_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value datetime)
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Default value str
    @a_default_value_str varchar(80),
    --Customization level
    @a_customization_level numeric(10),
    --Result
    @a_result numeric(1),
    --Old default value
    @a_old_default_value datetime,
    --Column id str
    @a_column_id_str varchar(32);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_datetime_columns', 'datetime', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_customization_level = customization_level,
    @a_old_default_value = default_value
    from meta_datetime_columns
    where column_id = @a_column_id;
    
    --If this is a system column
    if @a_customization_level = 0
    begin
        raiserror('Changing datetime system column default value is forbidden (column_id = %s) (#error_code = 20008)',
        16, 1, @a_column_id_str);
        return;
    end;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
		return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
		exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_datetime_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
    begin
        set @a_default_value_str = convert(varchar, @a_default_value, 9);
                
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ''' + @a_default_value_str + ''' for ' + @a_column_name);
    end;       
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_datetime_col_constrs';

go

--Function of creating all datetime column constraints
create procedure p_create_datetime_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Minimum
	@a_minimum datetime,
	--Maximum
	@a_maximum datetime;
	
	select @a_minimum = minimum,
	@a_maximum = maximum
	from meta_datetime_columns
	where column_id = @a_column_id;
	
	if @a_minimum is not null
		exec dbo.p_create_datetime_minimum_check @a_column_id;
	if @a_maximum is not null
		exec dbo.p_create_datetime_maximum_check @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_datetime_col_constrs';

go

--Function of droppping all datetime column constraints
create procedure p_drop_datetime_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	declare
	--Minimum
	@a_minimum datetime,
	--Maximum
	@a_maximum datetime;
	
	select @a_minimum = minimum,
	@a_maximum = maximum
	from meta_datetime_columns
	where column_id = @a_column_id;
	
	if @a_minimum is not null
		exec dbo.p_drop_datetime_minimum_check @a_column_id;
	if @a_maximum is not null
		exec dbo.p_drop_datetime_maximum_check @a_column_id;
end;